package com.example.demo.utils;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.stereotype.Component;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
public class ExcelUtils {

    public Map<Integer, String > getColumByColumnName(Sheet sheet, String columnName) {
        Map<Integer, String> map = new HashMap<>();
        Map<Integer, List<String>> sheetValues = readSheet(sheet);
        Integer columnNum = findColumnByName(columnName, sheet);
        if(columnName != null) {
            sheetValues.forEach(
                    (rowNum, rowValues) -> {
                        String value = rowValues.get(columnNum);
                        map.put(rowNum, value);
                    }
            );
        }
        return map;
    }

    public Map<Integer, List<String>> readSheet(Sheet sheet) {
        Map<Integer, List<String>> map = new HashMap<>();
        sheet.rowIterator().forEachRemaining(
                r -> {
                    Integer rowNum = r.getRowNum();
                    List<String> rowValues = readRowString(r);
                    map.put(rowNum, rowValues);
                }
        );
        return map;
    }

    private List<String> readRowString(Row row) {
        List<String> list = new ArrayList<>();
        row.cellIterator().forEachRemaining(
                c -> {
                    list.add(readCellString(c));
                }
        );
        return list;
    }


    private String readCellString(Cell cell) {
        return cell.getStringCellValue();
    }

    private Integer findColumnByName(String columnName, Sheet sheet) {
        Row title = sheet.getRow(1);
        Integer columnNum = null;
        Cell cell = null;
        if(title != null) {
            for(int i = title.getFirstCellNum(); i<title.getLastCellNum(); i++) {
                Cell cel1 = title.getCell(i);
                if(columnName.equals(readCellString(cell))) {
                    return i;
                }
            }
        }
        return null;
    }

    public Workbook getWorkBook(FileInputStream fis) throws IOException {
        Workbook workbook = new XSSFWorkbook(fis);
        return workbook;
    }

    public Sheet getSheetByName(Workbook workbook, String sheetName) {
        return workbook.getSheet(sheetName);
    }

}
